import xlrd
workbook = xlrd.open_workbook('./测试.xls')
booksheet = workbook.sheet_by_index(0)         #用索引取第一个sheet
# booksheet = workbook.sheet_by_name('Sheet 1')  #或用名称取sheet
#读单元格数据
# cell_11 = booksheet.cell_value(0,0)
# print(cell_11)
# cell_21 = booksheet.cell_value(1,0)
#读一行数据
#总行数
# print(booksheet.nrows)
#总列数
# print(booksheet.ncols)
all_result={}
key_words={'fam':388,'hex':521,'rox':526,'cy5':1388}
for i in range(1,booksheet.nrows):
    number=[]
    number.append(booksheet.cell(i,4).value)
    number.append(booksheet.cell(i,7).value)
    number.append(booksheet.cell(i,8).value)
    number.append(booksheet.cell(i,9).value)
    number.append(booksheet.cell(i,10).value)
    ccc={'/':-100,'':-100}
    number=[ccc[i] if i in ccc else i for i in number]
    # print(number)
    if -1.5<=number[1]<=1.5 or -1.5<=number[2]<=1.5:
        if -1.5<=number[3]<=1.5 or -1.5<=number[4]<=1.5:
            temp=[]
            temp.append(booksheet.cell(i,4).value)
            temp.append(str(key_words[str(booksheet.cell(i,4).value)])+'杂合突变')
            all_result[i]=temp
        else:
            temp=[]
            temp.append(booksheet.cell(i,4).value)
            temp.append(str(key_words[str(booksheet.cell(i,4).value)])+'纯合突变')
            all_result[i]=temp
    elif  abs(number[1])>1.5 and abs(number[2])>1.5 and -1.5<=number[3]<=1.5 or -1.5<=number[4]<=1.5:
        temp=[]
        temp.append(booksheet.cell(i,4).value)
        temp.append(str(key_words[str(booksheet.cell(i,4).value)])+'野生')
        all_result[i]=temp
    
all_result=sorted(all_result.items(), key=lambda item:item[0])
# print(all_result)
# row_3 = booksheet.row_values(0)
# print(row_3)
# col_value = booksheet.col_values(7)
# print(col_value)
import xlwt # 貌似不支持excel2007的xlsx格式
newWorkbook = xlwt.Workbook()
newWorksheet = newWorkbook.add_sheet(workbook.sheet_names()[0])
newWorksheet.write(0,0,'fam')
newWorksheet.write(0,1,'hex')
newWorksheet.write(0,2,'rox')
newWorksheet.write(0,3,'cy5')
for i in all_result:
    try:
        if i[1][0] == 'fam':
            newWorksheet.write(i[0],0,i[1][1])
        elif i[1][0] == 'hex':
            newWorksheet.write(i[0],1,i[1][1])
        elif i[1][0] == 'rox':
            newWorksheet.write(i[0],2,i[1][1])
        elif i[1][0] == 'cy5':
            newWorksheet.write(i[0],3,i[1][1])
    except:
        pass
newWorkbook.save('excelwrite.xls')